﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
#if Excel
using Excel;

using OfficeOpenXml;
#endif
using UnityEngine;

namespace UFrame
{
    /// <summary>
    /// 负责读取excel 和 保存excel
    /// </summary>
    public class ExcelHelper
    {
        public static int fieldRow = 1;

        /// <summary>
        /// 字段名在一个单元格:分割之后 第几列
        /// </summary>
        public static int fieldCol = 0;

        public static int[] ignoreRow = new[] { 0, 1, 2 };

#if Excel
        private static IExcelDataReader GetExcelRead(string fileName)
        {
            FileStream stream = null;
            try
            {
                stream = File.Open(fileName, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite);
            }
            catch (Exception ex)
            {
                Debug.LogError("请关闭" + fileName + " " + ex.Message);
                return null;
            }


            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            return excelReader;
        }
#endif

        private static Dictionary<string, int> GetFieldLocal(string className, DataTable dc)
        {
            Dictionary<string, int> fieldDic = new Dictionary<string, int>();
            Type tType = AssemblyTool.SearchType(className);
            var fields = tType.GetFields(BindingFlags.Instance | BindingFlags.Public);
            DataRow excelFieldRow = dc.Rows[fieldRow];

            for (int i = 0; i < excelFieldRow.ItemArray.Length; i++)
            {
                string f = excelFieldRow.ItemArray[i] as string;
                if (f == null)
                {
                    continue;
                }

                string excelField = null;
                if (f.Contains(":"))
                {
                    string[] excelFieldAry = f.Split(':');
                    excelField = excelFieldAry[fieldCol];
                }
                else
                {
                    excelField = f;
                }

                foreach (var classFields in fields)
                {
                    if (excelField == classFields.Name)
                    {
                        fieldDic.Add(classFields.Name, i);
                    }
                }
            }

            return fieldDic;
        }


        public static List<T> GetAllRow<T>(string fileName, string className) where T : class, new()
        {
#if Excel
            IExcelDataReader excelReader = GetExcelRead(fileName);
            if (excelReader == null)
                return new List<T>();

            DataSet result = excelReader.AsDataSet();
            var dc = result.Tables[0];

            Type tType = AssemblyTool.SearchType(className);

            var fields = tType.GetFields(BindingFlags.Instance | BindingFlags.Public);
            Dictionary<string, int> fieldDic = GetFieldLocal(className, dc);

            List<T> list = new List<T>();
            string fieldName = string.Empty;
            string fieldTypeName = string.Empty;
            object objVal = null;
            int row = 0;
            int col = 0;
            //int maxCol = 10;

            try
            {
                for (int i = 0; i < dc.Rows.Count; i++)
                {
                    if (ignoreRow.Contains(i))
                        continue;

                    row = i;
                    T t = (T) Activator.CreateInstance(tType);

                    foreach (var item in fields)
                    {
                        if (!fieldDic.ContainsKey(item.Name))
                            continue;

                        if (dc.Rows[i].ItemArray.Length <= col)
                        {
                            Debug.LogError("不存在" + col);
                        }

                        col = fieldDic[item.Name];
                        objVal = dc.Rows[i][col];

                        fieldName = item.Name;
                        fieldTypeName = item.FieldType.Name;
                        SetRowData(fieldTypeName, t, item, objVal);
                    }

                    list.Add(t);
                }
            }
            catch (Exception ex)
            {
                string notFileName = Path.GetFileNameWithoutExtension(fileName);
                if (CheckNullRow(fields, fieldDic, dc, row) == false)
                {
                    Debug.LogError(" fieldName:" + fieldName + " objVal:" + objVal + "  " + fieldTypeName + " 行:" +
                                   row +
                                   " 列:" +
                                   col + " " + notFileName + " " +
                                   ex.Message);
                }

                return list;
            }


            excelReader.Close();
            return list;
#else
            return null;
#endif
        }

        public static bool CheckNullRow(FieldInfo[] fields, Dictionary<string, int> fieldDic, DataTable dc, int row)
        {
            int col = 0;
            foreach (var item in fields)
            {
                if (!fieldDic.ContainsKey(item.Name))
                {
                    continue;
                }

                col = fieldDic[item.Name];
                if (string.IsNullOrEmpty(dc.Rows[row][col].ToString()) == false)
                {
                    return false;
                }
            }

            return true;
        }

        public static T GetRow<T>(string fileName, string className, int id) where T : class, new()
        {
#if Excel
            IExcelDataReader excelReader = GetExcelRead(fileName);
            DataSet result = excelReader.AsDataSet();

            var dc = result.Tables[0];

            Type tType = AssemblyTool.SearchType(className);
            var fields = tType.GetFields(BindingFlags.Instance | BindingFlags.Public);
            var fieldDic = GetFieldLocal(className, dc);

            T t = null;
            string fieldName = string.Empty;
            string fieldTypeName = string.Empty;
            object objVal = null;
            int row = 0;
            int col = 0;
            try
            {
                string strId = id.ToString();
                for (int i = 0; i < dc.Rows.Count; i++)
                {
                    row = i;
                    string val = dc.Rows[i][0].ToString();
                    if (val == strId)
                    {
                        t = (T) Activator.CreateInstance(tType);
                        foreach (var item in fields)
                        {
                            if (!fieldDic.ContainsKey(item.Name))
                                continue;

                            col = fieldDic[item.Name];
                            objVal = dc.Rows[i][col];

                            fieldName = item.Name;
                            fieldTypeName = item.FieldType.Name;

                            SetRowData(fieldTypeName, t, item, objVal);
                        }

                        break;
                    }
                }
            }
            catch (Exception ex)
            {
                string notFileName = Path.GetFileNameWithoutExtension(fileName);
                if (CheckNullRow(fields, fieldDic, dc, row) == false)
                {
                    Debug.LogError(" fieldName:" + fieldName + " objVal:" + objVal + "  " + fieldTypeName + " 行:" +
                                   row +
                                   "   列: " +
                                   col + " " + notFileName +
                                   ex.Message);
                }

                return null;
            }


            excelReader.Close();
            return t;

#else
            return null;
#endif
        }


        public static void SetRow(string fileName, int id, Dictionary<string, object> pair)
        {
            var fileinfo = new FileInfo(fileName);
#if Excel
            using (ExcelPackage myExcelPackage = new ExcelPackage(fileinfo))
            {
                var sheet = myExcelPackage.Workbook.Worksheets[1];
                int cols = 50;

                var excelFieldRow = cols;

                Dictionary<string, int> fieldDic = new Dictionary<string, int>();
                Dictionary<int, object> fieldDic2 = new Dictionary<int, object>();
                for (int i = 1; i < excelFieldRow; i++)
                {
                    //因为这个dll是从1开始的
                    string f = sheet.Cells[fieldRow + 1, i].Value as string;
                    if (f == null)
                        continue;

                    string excelField = null;
                    if (f.Contains(":"))
                    {
                        string[] excelFieldAry = f.Split(':');
                        excelField = excelFieldAry[0];
                    }
                    else
                    {
                        excelField = f;
                    }

                    foreach (var classFiedls in pair)
                    {
                        if (excelField == classFiedls.Key)
                        {
                            fieldDic.Add(classFiedls.Key, i);
                            fieldDic2.Add(i, classFiedls.Value);
                        }
                    }
                }

                bool isSearchRow = false;
                string strId = id.ToString();
                for (int row = 1; sheet.Cells[row, 1].Value != null || row < 3; row++)
                {
                    string val = sheet.Cells[row, 1].Value.ToString();
                    if (val == strId)
                    {
                        isSearchRow = true;
                        foreach (var item in pair)
                        {
                            if (fieldDic.ContainsKey(item.Key) == false)
                                continue;

                            int col = fieldDic[item.Key];
                            string name = item.Key;
                            object objSetVal = item.Value;
                            SetRowData(name, objSetVal, sheet, row, col);
                        }

                        myExcelPackage.Workbook.Calculate();
                        sheet.Calculate();
                        break;
                    }
                }

                if (isSearchRow)
                {
                    myExcelPackage.Save();
                    Debug.Log("保存成功");
                }
                else
                {
                    Debug.Log("没有找到metaId" + id);
                }
            }
#endif
        }



        public static void SetRow<T>(string fileName, T t, int id) where T : class, new()
        {

#if Excel
            if (t == null)
            {
                Debug.LogError("Meta数据没有无法保存");
                return;
            }

            var fileinfo = new FileInfo(fileName);

            using (ExcelPackage myExcelPackage = new ExcelPackage(fileinfo))
            {
                ExcelWorksheet sheet = myExcelPackage.Workbook.Worksheets[1];
                int cols = 50;

                var fields = t.GetType().GetFields(BindingFlags.Instance | BindingFlags.Public);
                var excelFieldRow = cols;

                Dictionary<string, int> fieldDic = new Dictionary<string, int>();
                Dictionary<int, FieldInfo> fieldDic2 = new Dictionary<int, FieldInfo>();
                for (int i = 1; i < excelFieldRow; i++)
                {
                    //因为这个dll是从1开始的
                    string f = sheet.Cells[fieldRow + 1, i].Value as string;
                    if (f == null)
                        continue;

                    string excelField = null;
                    if (f.Contains(":"))
                    {
                        string[] excelFieldAry = f.Split(':');
                        excelField = excelFieldAry[fieldCol];
                    }
                    else
                    {
                        excelField = f;
                    }

                    foreach (var classFields in fields)
                    {
                        if (excelField == classFields.Name)
                        {
                            fieldDic.Add(classFields.Name, i);
                            fieldDic2.Add(i, classFields);
                        }
                    }
                }

                bool isSearchRow = false;
                string strId = id.ToString();
                for (int row = 1; sheet.Cells[row, 1].Value != null || row < 3; row++)
                {
                    string val = sheet.Cells[row, 1].Value.ToString();
                    if (val == strId)
                    {
                        isSearchRow = true;
                        foreach (var item in fields)
                        {
                            int col = fieldDic[item.Name];
                            var field = fieldDic2[col];
                            SetRowData(field.Name, field.GetValue(t), sheet, row, col);
                        }

                        myExcelPackage.Workbook.Calculate();
                        sheet.Calculate();
                        break;
                    }
                }

                if (isSearchRow)
                {
                    myExcelPackage.Save();
                    Debug.Log("保存成功");
                }
                else
                {
                    Debug.Log("没有找到metaId" + id);
                }
            }
#endif
        }


        public static void SetRowData(string fieldTypeName, object t, FieldInfo item, object objVal)
        {
            if (fieldTypeName == "Int32")
            {
                item.SetValue(t, Convert.ToInt32(objVal));
            }
            else if (fieldTypeName == "Float")
            {
                item.SetValue(t, Convert.ToSingle(objVal));
            }
            else if (fieldTypeName == "Single")
            {
                item.SetValue(t, Convert.ToSingle(objVal));
            }
            else if (fieldTypeName == "Double")
            {
                item.SetValue(t, Convert.ToDouble(objVal));
            }
            else if (fieldTypeName == "Boolean")
            {
                bool o = objVal == "1";
                item.SetValue(t, o);
            }
            else if (fieldTypeName == "DBNull")
            {
                item.SetValue(t, null);
            }
            else if (fieldTypeName == "String")
            {
                item.SetValue(t, objVal.ToString());
            }
            else
            {
                if (objVal != null)
                {
                    item.SetValue(t, objVal);
                }
                else
                {
                    Debug.Log("没有设置: " + item.Name);
                }
            }
        }

#if Excel
        public static void SetRowData(string name, object objSetVal, ExcelWorksheet sheet, int i, int col)
        {
            object objVal = sheet.Cells[i, col];

            if (name == "Int32")
            {
                sheet.SetValue(i, col, Convert.ToInt32(objSetVal));
            }
            else if (name == "Float")
            {
                sheet.Cells[i, col].Value = Convert.ToSingle(objSetVal);
            }
            else if (name == "Single")
            {
                sheet.Cells[i, col].Value = Convert.ToSingle(objSetVal);
            }
            else if (name == "Double")
            {
                sheet.Cells[i, col].Value = Convert.ToDouble(objSetVal);
            }
            else if (name == "Boolean")
            {
                sheet.Cells[i, col].Value = Convert.ToBoolean(objSetVal) ? 1 : 0;
            }
            else if (name == "DBNull")
            {
                sheet.Cells[i, col].Value = null;
            }
            else if (name == "String")
            {
                sheet.Cells[i, col].Value = objSetVal.ToString();
            }
            else
            {
                if (objVal != null)
                    sheet.Cells[i, col].Value = objSetVal;
                else
                {
                    Debug.Log("没有设置: " + name);
                }
            }
        }

#endif
    }
}